package ar.com.mooral.data;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;
import ar.com.mooral.crohn.Crohn;
import ar.com.mooral.crohn.Synchronizer;
import ar.com.mooral.pojo.BasePojo;



public abstract class DatabaseHelper extends SQLiteOpenHelper {

	static final String dbName = "crohnDB";
	static final String confTable = "configuraciones";
	static final String updField = "fechaModificacion";
	
	protected SharedPreferences settings;
	protected SQLiteDatabase sqLiteDatabase = null;
	
	protected abstract String getTableName();
	public abstract String getShortTableName();
	public abstract Fields getFields();

	private Context context = null;
	
	
	public DatabaseHelper(Context context) {

		super(context, dbName, null, 1);
		this.context = context;
		
		this.settings = context.getSharedPreferences(Crohn.PREFS_NAME, 0);
		

		/*
		try {
			// Check if the table is not created
			boolean isTableCreated = false;
			
			Cursor c = this.getDb().rawQuery("select name from sqlite_master where type = 'table'", null);
			c.moveToFirst();
	        while (c.isAfterLast() == false) {
	        	if (this.getTableName().equals(c.getString(0))) {
	        		isTableCreated = true;
	        		break;
	        	}
	       	    c.moveToNext();
	        }
	        c.close();
	        
	        if (!isTableCreated) {
	        	this.getDb().execSQL(this.getCreateQuery());
	        }
	        
		} catch(Exception e) {
			Log.v("CROHN", "Exception: " + e.getMessage());
		}
		
		*/
        
	}
	
	
	public static long dateToTimestamp(String sDate) {

       long lDate = 0;
       
       try {
           //2011-02-15 14:47:28
           //SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	   SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    	   Date date = (Date)formatter.parse(sDate);
    	   lDate = date.getTime();
       } catch (ParseException e) {
    	   Log.v("CROHN", "ParseException: " + e.getMessage());
       }
       
       return lDate;
 	}    

	
	public static String getMysqlDate(String sDate) {

		String r = "";
		
		try {
			SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
			Date date = (Date)formatter.parse(sDate);
   
			SimpleDateFormat mysqlFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			r = mysqlFormatter.format(date);

       } catch (ParseException e) {
    	   	Log.v("CROHN", "ParseException: " + e.getMessage());
       }
	   
       return r;
	}    


	public static String getFrenchDate(String sDate) {

		String r = "";
		
		try {
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date date = (Date)formatter.parse(sDate);
   
			SimpleDateFormat mysqlFormatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
			r = mysqlFormatter.format(date);

       } catch (ParseException e) {
    	   	Log.v("CROHN", "ParseException: " + e.getMessage());
       }
	   
       return r;
	}    
	

	public static String timestampToDate(long lDate) {

		//2011-02-15 14:47:28
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return formatter.format(new Date(lDate));
	}    
	
	
	public void doInserts() {}

/**
 * Intent to create a table
 * 
 */
	public void create() {
		try {
			this.getDb().execSQL(this.getCreateQuery());
			this.doInserts();
			this.closeDb(); // TODO Prueba
		} catch(Exception e) {
			Log.v("CROHN", "Exception: " + e.getMessage());
		}
	}

	
/**
 * Converts a HashTable into a ContentValue object
 * 
 * @param data
 * @return
 */
	protected ContentValues getContentValues(Hashtable<String, String> data) {

	    ContentValues cv = new ContentValues();
		
	    Enumeration<String> e = data.keys();
	    while (e.hasMoreElements()) {
	    	String key = (String) e.nextElement();
	    	cv.put(key, (String) data.get(key));
	    }
	    
	    return cv;
	}


	private String getFieldList(boolean alsoExtraInfo, boolean alsoExtraFields) {
		
/*		Hashtable<String, String> data = this.getFields();
		String[] fields = null;
		if (alsoExtraFields) {
			fields = new String[data.size() + 4];
		} else {
			fields = new String[data.size()];
		}
	    Enumeration<String> e = data.keys();
	    int i = 0;
	    while (e.hasMoreElements()) {
	    	String key = (String) e.nextElement();
	    	if (alsoExtraInfo) {
	    		fields[i++] = key + " " + (String) data.get(key);
	    	} else {
	    		fields[i++] = key;
	    	}
	    }*/
		
		Fields data = this.getFields();
		String[] fields = null;
		if (alsoExtraFields) {
			fields = new String[data.size() + 4];
		} else {
			fields = new String[data.size()];
		}
		

		/*
		Iterator ite = ((List<Hashtable<String, String>>) this.getFields()).iterator();
		while (ite.hasNext()) {
			//System.out.println(h.get(ite.next()));

		}
		*/
		
        
		Iterator<String> ite = data.getFieldNames().iterator();
	    int i = 0;
	    while (ite.hasNext()) {
	    	String key = (String) ite.next();
	    	if (alsoExtraInfo) {
	    		fields[i++] = key + " " + (String) data.get(key);
	    	} else {
	    		fields[i++] = key;
	    	}
	    }
		
		
	    if (alsoExtraFields) {
	    	if (alsoExtraInfo) {
	    		fields[i++] = "remoteId INTEGER";
	    		fields[i++] = "addControlField INTEGER";
	    		fields[i++] = "editControlField INTEGER";
	    		fields[i++] = "deleteControlField INTEGER";
	    	} else {
	    		fields[i++] = "remoteId";
	    		fields[i++] = "addControlField";
	    		fields[i++] = "editControlField";
	    		fields[i++] = "deleteControlField";
	    	}
	    }
	    
	    return TextUtils.join(", ", fields);
	}
	
	private String getCreateQuery() {
		return "CREATE TABLE " + this.getTableName() + " (" + this.getFieldList(true, true) + ");";
	}


	public void drop() {
		this.getDb().execSQL("DROP TABLE " + this.getTableName() + ";");
		this.closeDb(); // TODO Prueba

	}

	public Result save(Hashtable<String, String> data) {
		if (data.containsKey("id")) {
			return this.update(data, true);
		} else {
			return this.insert(data, true);
		}
	}

	
	public Result insert(Hashtable<String, String> data) {
		return this.insert(data, true);
	}

	public Result insert(Hashtable<String, String> data, boolean markControlField) {

		// Mark data for synchronization
		data.put("editControlField", "0");
		data.put("deleteControlField", "0");
		if (markControlField) {
			data.put("addControlField", "1");
		} else {
			data.put("addControlField", "0");
		}
		
		long r = this.getDb().insert(this.getTableName(), "id", this.getContentValues(data));
		this.closeDb(); // TODO Prueba

		Result result = new Result(r);
		if (r < 0) {
			result.setTitle(this.getTableName());
			result.setDescription("No fue posible insertar el nuevo registro.");
		}
		
		if (markControlField) {
			Synchronizer sync = new Synchronizer(this.context);
			sync.sendUpdates(this);
		}
		
		return result;
	}


	public Result update(Hashtable<String, String> data) {
		return this.update(data, true);
	}
	
	public Result update(Hashtable<String, String> data, boolean markControlField) {
		
		// Mark data for synchronization 
		data.put("addControlField", "0");
		data.put("deleteControlField", "0");
		if (markControlField) {
			data.put("editControlField", "1");
		} else {
			data.put("editControlField", "0");
		}
		
		long r = this.getDb().update(this.getTableName(), this.getContentValues(data), "id = " + data.get("id"), null);
		this.closeDb(); // TODO Prueba

		Result result = new Result(r);
		if (r < 0) {
			result.setTitle(this.getTableName());
			result.setDescription("No fue posible actualizar el registro.");
		}
		
		if (markControlField) {
			Synchronizer sync = new Synchronizer(this.context);
			sync.sendUpdates(this);
		}
		
		return result;
	}

	public Result delete(String id) {
		return this.delete(id, true);
	}
	
	public Result delete(String id, boolean markControlField) {
		
		long r = 0;
		
		if (markControlField) {
	
			Hashtable<String, String> data = new Hashtable<String, String>();
			data.put("deleteControlField", "1");
			
			r = this.getDb().update(this.getTableName(), this.getContentValues(data), "id = " + id, null);
			this.closeDb(); // TODO Prueba

			
		} else {
			r = this.getDb().delete(this.getTableName(), "id = " + id, null);
			this.closeDb(); // TODO Prueba

		}
		
		return new Result(r);
	}
	

	public Vector<Hashtable<String, String>> findAllAdds() {
		Hashtable<String, String> data = new Hashtable<String, String>(); 
		data.put("addControlField", "1");
		return this.findAll(data, true);
	}
	
	public Vector<Hashtable<String, String>> findAllEdits() {
		Hashtable<String, String> data = new Hashtable<String, String>(); 
		data.put("editControlField", "1");

		String sFields = this.getFieldList(false, false) + ", remoteId";
		return this.findAll(data, true, sFields);
		
		//return this.findAll(data, true);
	}

	public Vector<Hashtable<String, String>> findAllDeletes() {
		Hashtable<String, String> data = new Hashtable<String, String>(); 
		data.put("deleteControlField", "1");
		return this.findAll(data, true);
	}

	public Vector<Hashtable<String, String>> findAll(String minRange, String maxRange) {
		
		// find all no soft deleted records 
		Hashtable<String, String> data = new Hashtable<String, String>(); 
		data.put("deleteControlField", "0");
		
		Vector<Hashtable<String, String>> r = this.findAll(data);
		
		
		long lMinRange = 0;
		long lMaxRange = Long.MAX_VALUE;
		if (minRange != null) {
			lMinRange = DatabaseHelper.dateToTimestamp(minRange);
		}
		if (maxRange != null) {
			lMaxRange = DatabaseHelper.dateToTimestamp(maxRange);
		}


		if (minRange != null || maxRange != null) {
			for (int i = 0; i < r.size(); i++) {
				
				long d = DatabaseHelper.dateToTimestamp(((BasePojo)r.get(i)).getDate());
				if (d < lMinRange || d > lMaxRange) {
					r.remove(i);
					i--;
				}
			}
		}
		
		return r;
	}
	
	public Vector<Hashtable<String, String>> findAll() {
		
		// find all no soft deleted records 
		Hashtable<String, String> data = new Hashtable<String, String>(); 
		data.put("deleteControlField", "0");
		
		return this.findAll(data);
	}
	
	public Vector<Hashtable<String, String>> findAll(Hashtable<String, String> conditions) {
		return this.findAll(conditions, false);
	}

	
	public Vector<Hashtable<String, String>> findAll(Hashtable<String, String> conditions, boolean formatMysql) {
		String sFields = this.getFieldList(false, false);
		return this.findAll(conditions, formatMysql, sFields);
	}	
	
	public Vector<Hashtable<String, String>> findAll(Hashtable<String, String> conditions, boolean formatMysql, String sFields) {
		
		
		
		Vector<Hashtable<String, String>> r = new Vector<Hashtable<String, String>>();
		Cursor c = null;
		
		String ands = "";
		
		if (conditions != null) {
			
			String[] fields = new String[conditions.size()];

			try {
			    Enumeration e = conditions.keys();
			    int i = 0;
			    while (e.hasMoreElements()) {
			    	String key = (String) e.nextElement();
			    	fields[i++] = key + " = '" + (String)conditions.get(key) + "'";
			    }
			} catch (Exception e) {
				Log.v("CROHN", "Exception: " + e.getMessage());
			}
		    
		    ands += " AND " + TextUtils.join(" AND ", fields);
		}

		
		try {
			
			//String sFields = this.getFieldList(false, false);
			//String[] fields = sFields.split(", ");
			
			c = this.getDb().rawQuery("SELECT " + sFields + " FROM " + this.getTableName() + " WHERE 1=1" + ands, null);
			
			c.moveToFirst();
			
			int columnCount = c.getColumnCount();
			

	        while (c.isAfterLast() == false) {
	        
	        	Hashtable<String, String> data = buildPojo();
	        	for (int i = 0; i < columnCount; i++) {
	        		String field = c.getColumnName(i);
        			String value = c.getString(i);
        			if (value == null) {
        				value = "";
        			}
        			
        			// Format date to be sended
			    	if (formatMysql && field.equals("fecha")) {
			    		value = DatabaseHelper.getMysqlDate(value);
			    	}
        			
        			data.put(field, value);
	        	}
	        	r.add(data);
	       	    c.moveToNext();
	        }
	        c.close();
			
		} catch (Exception e) {
			Log.v("CROHN", "Exception: " + e.getMessage());
		}
		finally{
			this.closeDb(); // TODO Prueba
		}
		
		return r;
	}	
	
	public String rawQueryForSingleValue(String sql, String field) {
		
		Cursor c = this.getDb().rawQuery(sql, null);
		c.moveToFirst();
		int index = c.getColumnIndex(field);
		String ret = c.getString(index);
		
		c.close(); // TODO prueba
		this.closeDb(); // TODO Prueba

		return ret;
	}
	

	public String getLocalId(String remoteId) {
		String r = "";
		try {
			r = this.rawQueryForSingleValue("SELECT id FROM " + this.getTableName() + " WHERE remoteId = " + remoteId, "id");
		} catch(Exception e) {
			Log.v("CROHN", "Exception: " + e.getMessage());
		}
		return r;
	}
	
	

	public void onCreate(SQLiteDatabase db) {

	}
	

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}



/**
 * Get current db instance.
 * @return
 */
	 protected SQLiteDatabase getDb() {
		 sqLiteDatabase = this.getWritableDatabase();
		 return sqLiteDatabase;
	 }

	 protected void closeDb() {
		 if(sqLiteDatabase != null){
			 if(sqLiteDatabase.isOpen())
				 sqLiteDatabase.close();
		 }
	 }

	 protected final BasePojo buildPojo(){
		 BasePojo basePojo = getNewPojo();
		 basePojo.setDatabaseHelper(this);
		 return basePojo;
	 }
	protected abstract BasePojo getNewPojo();
}